﻿

/*
SELECT * FROM vEmployeeTree vEmployeeTree ORDER BY EmployeeLevel, EmployeeName
SELECT * FROM vEmployeeTree vEmployeeTree where ParentEmployeeKey = 277
*/
CREATE VIEW [dbo].[vEmployeeTree] AS
WITH cte AS (
 SELECT
  EmployeeKey
 , FirstName + ' ' + LastName EmployeeName
 , ParentEmployeeKey
 , 1 EmployeeLevel
 , convert(varchar(255),EmployeeKey) as EmployeePath
 FROM dbo.DimEmployee
 WHERE ParentEmployeeKey IS NULL
	
 UNION ALL
 SELECT
  c.EmployeeKey
 , c.FirstName + ' ' + c.LastName
 , c.ParentEmployeeKey
 , p.EmployeeLevel + 1
 , convert(varchar(255),p.EmployeePath + '>'+ convert(varchar(10),c.EmployeeKey)) as EmployeePath
 FROM dbo.DimEmployee c
 JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey
)
SELECT isnull(cte.EmployeeKey,0) as EmployeeKey
	,nullif(cte.EmployeeName,'') as EmployeeName
	,nullif(cte.ParentEmployeeKey,0) as ParentEmployeeKey
	,nullif(cte.EmployeeLevel,0) as EmployeeLevel
	,nullif(cte.EmployeePath,'') as EmployeePath
	,nullif(SUM(f.SalesAmount), 0) as SalesAmount
FROM cte
LEFT JOIN dbo.FactResellerSales f ON f.EmployeeKey = cte.EmployeeKey
GROUP BY isnull(cte.EmployeeKey,0)
	,nullif(cte.EmployeeName,'')
	,nullif(cte.ParentEmployeeKey,0)
	,nullif(cte.EmployeeLevel,0)
	,nullif(cte.EmployeePath,'')